package org.zjvis.datascience.service.graph;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.stream.Collectors;
import java.util.stream.IntStream;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import com.google.common.collect.HashBiMap;
import org.apache.commons.lang3.SerializationUtils;
import org.apache.tinkerpop.gremlin.process.traversal.Path;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.zjvis.datascience.common.dto.TaskDTO;
import org.zjvis.datascience.common.dto.TaskInstanceDTO;
import org.zjvis.datascience.common.widget.dto.WidgetDTO;
import org.zjvis.datascience.common.dto.graph.GraphDTO;
import org.zjvis.datascience.common.enums.ActionEnum;
import org.zjvis.datascience.common.enums.TaskInstanceStatus;
import org.zjvis.datascience.common.exception.BaseErrorCode;
import org.zjvis.datascience.common.exception.DataScienceException;
import org.zjvis.datascience.common.graph.constant.GraphConstant;
import org.zjvis.datascience.common.graph.exporter.ExporterCSV;
import org.zjvis.datascience.common.graph.exporter.ExporterGML;
import org.zjvis.datascience.common.graph.exporter.ExporterJSON;
import org.zjvis.datascience.common.graph.model.CategoryCleanAction;
import org.zjvis.datascience.common.graph.model.GraphAttr;
import org.zjvis.datascience.common.graph.model.MetricResult;
import org.zjvis.datascience.common.graph.model.MetricResultManager;
import org.zjvis.datascience.common.model.ApiResultCode;
import org.zjvis.datascience.common.util.CollectionUtil;
import org.zjvis.datascience.common.graph.util.GraphUtil;
import org.zjvis.datascience.common.util.JwtUtil;
import org.zjvis.datascience.common.util.ToolUtil;
import org.zjvis.datascience.common.util.db.JDBCUtil;
import org.zjvis.datascience.common.vo.TaskInstanceVO;
import org.zjvis.datascience.common.vo.graph.*;
import org.zjvis.datascience.service.*;
import org.zjvis.datascience.service.dataprovider.GPDataProvider;
import org.zjvis.datascience.service.mapper.GraphMapper;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

import javax.servlet.http.HttpServletResponse;

/**
 * @description GraphService
 * @date 2021-12-29
 */
@Service
public class GraphService {
    private final static Logger logger = LoggerFactory.getLogger("GraphService");

    @Autowired
    private GraphMapper graphMapper;

    @Lazy
    @Autowired
    private TaskService taskService;

    @Autowired
    private GPDataProvider gpDataProvider;

    @Autowired
    private JanusGraphEmbedService janusGraphEmbedService;

    @Lazy
    @Autowired
    private WidgetService widgetService;

    @Autowired
    private FastTextService fastTextService;

    @Autowired
    private AutoJoinService autoJoinService;

    @Autowired
    private TColumnService tColumnService;

    @Autowired
    private TaskInstanceService taskInstanceService;

    @Lazy
    @Autowired
    private GraphActionService graphActionService;

    public static final String CREATE_VIEW_SQL = "CREATE VIEW %s AS %s";

    public static final String DROP_VIEW_SQL = "DROP VIEW %s";

    //view_(cid)_(timestamp)
    public static final String VIEW_TABLE_NAME = "graph.view_%s_%s";

    private ExecutorService executor = Executors.newFixedThreadPool(5);

    public void closeTransactions() {
        //查询数据时防止线程之间事务泄漏，必须关闭事务
        janusGraphEmbedService.rollback();
    }

    public Long save(GraphDTO graph) {
        graphMapper.save(graph);
        return graph.getId();
    }

    public boolean update(GraphDTO graph) {
        return graphMapper.update(graph);
    }

    public GraphDTO getEmptyGraphByTaskId(Long tid) {
        TaskDTO taskDTO = taskService.queryById(tid);
        GraphDTO emptyGraphDTO = new GraphDTO();
        emptyGraphDTO.setUserId(JwtUtil.getCurrentUserId());
        emptyGraphDTO.setProjectId(taskDTO.getProjectId());
        emptyGraphDTO.setPipelineId(taskDTO.getPipelineId());
        emptyGraphDTO.setTaskId(taskDTO.getId());
        emptyGraphDTO.setName(taskDTO.getName());
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("categories", new ArrayList<>());
        jsonObject.put("edges", new ArrayList<>());
        jsonObject.put("nodes", new ArrayList<>());
        jsonObject.put("links", new ArrayList<>());

        String janusGraphName = "graph_" + JwtUtil.getCurrentUserId() + "_" + System.currentTimeMillis();
        jsonObject.put("janusGraphName", janusGraphName);
        emptyGraphDTO.setDataJson(jsonObject.toJSONString());
//        janusGraphService.createConfiguredGraph(janusGraphName);
        return emptyGraphDTO;
    }

    public GraphDTO queryByTaskId(Long tid){
        GraphDTO graphDTO = graphMapper.queryByTaskId(tid);
        return graphDTO;
    }

    public GraphDTO queryById(Long gid){
        return graphMapper.queryById(gid);
    }

    public List<GraphDTO> queryByProjectId(Long pid) {
        return graphMapper.queryByProjectId(pid);
    }

    public List<GraphDTO> queryByProjectIdFull(Long pid) {
        return graphMapper.queryByProjectIdFull(pid);
    }

    public List<String> queryNamesByProjectId(Long pid) {
        return graphMapper.queryNamesByProjectId(pid);
    }

    public Long queryUserById(Long gid) {
        return graphMapper.queryUserById(gid);
    }

    public Long queryProjectById(Long gid) {
        return graphMapper.queryProjectById(gid);
    }

    public void deleteById(Long id) {
        graphMapper.deleteById(id);
        graphActionService.deleteByGraphId(id);
        executor.submit(new Runnable() {
            @Override
            public void run() {
                janusGraphEmbedService.dropAllGraphNodes(id);
            }
        });
    }

    public TaskInstanceDTO queryLatestActionByParentId(String cid) {
        return graphMapper.queryLatestActionByParentId(cid);
    }

    public List<TaskInstanceDTO> queryActionByParentIdAndOrder(String cid) {
        return graphMapper.queryActionByParentIdAndOrder(cid);
    }

    public void deleteByTaskId(Long tid) {
        GraphDTO graphDTO = this.queryByTaskId(tid);
        if (graphDTO != null) {
            this.deleteById(graphDTO.getId());
        }
    }

    public void closeGraphSession(Long gid) {
    }

    public CategoryVO getCategoryByCId(Long gid, String cid) {
        GraphDTO graphDTO = this.queryById(gid);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        List<CategoryVO> categories = obj.getJSONArray("categories").toJavaList(CategoryVO.class);
        Optional <CategoryVO> opt = categories.stream().filter(c -> c.getId().equals(cid)).findFirst();
        if (opt.isPresent()) {
            return opt.get();
        } else {
            throw DataScienceException.of(BaseErrorCode.GRAPH_ELEMENT_NOT_FOUND, "" + cid);
        }
    }

    public List<CategoryVO> getCategoryByCId(Long gid, List<String> cids) {
        GraphDTO graphDTO = this.queryById(gid);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        List<CategoryVO> categories = obj.getJSONArray("categories").toJavaList(CategoryVO.class);
        List<CategoryVO> filter = categories.stream().filter(c -> cids.contains(c.getId())).collect(Collectors.toList());
        return filter;
    }


    public CategoryVO addCategory(Long gid, CategoryVO vo, GraphVO context) {
        GraphDTO graphDTO = this.queryById(gid);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        List<CategoryVO> categories = obj.getJSONArray("categories").toJavaList(CategoryVO.class);
        String cid = GraphUtil.genCategoryId(gid, categories);
        vo.setId(cid);
        vo.setValue(queryCategoryValue(vo));
        String keyAttrId = cid + "_" + System.currentTimeMillis();
        vo.getKeyAttr().setId(keyAttrId);
        vo.getOriginLabel().setId(keyAttrId);
        JSONObject tableInfo = vo.getTableInfo();
        String originTableName = tableInfo.getString("tableName");
        String viewTableName = this.createCategoryViewTable(originTableName, cid);
        tableInfo.put("tableName", viewTableName);
        tableInfo.put("originTableName", originTableName);
        vo.setTableInfo(tableInfo);
        //插入一条原始数据记录
        this.addOriginalAction(graphDTO, vo);
        categories.add(vo);
        obj.put("categories", categories);
        graphDTO.setDataJson(obj.toJSONString());
        this.update(graphDTO);

        if (context != null) {
            graphDTO.initActionView(context);
            context.getCategories().add(vo);
        }
        return vo;
    }

    public EdgeVO addEdge(Long gid, EdgeVO vo, GraphVO context) {
        GraphDTO graphDTO = this.queryById(gid);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        List<EdgeVO> edges = obj.getJSONArray("edges").toJavaList(EdgeVO.class);
//        if (!checkEdge(edges, vo)) {
//            return null;
//        }
        String eid = GraphUtil.genEdgeId(gid, edges);
        vo.setId(eid);
        vo.setAttrs(new ArrayList<>());
        vo.setAttrIds(new ArrayList<>());
        vo.setWeight("null");
        vo.setWeightCfg(new JSONObject());
        edges.add(vo);
        obj.put("edges", edges);
        graphDTO.setDataJson(obj.toJSONString());
        this.update(graphDTO);
        if (context != null) {
            graphDTO.initActionView(context);
            context.getEdges().add(vo);

        }
        return vo;
    }

    public boolean checkEdge(List<EdgeVO> edges, EdgeVO newEdge) {
        Set<Set<String>> edgeHelper = new HashSet<>();
        for (EdgeVO edge: edges) {
            Set<String> tmp = new HashSet<>();
            tmp.add(edge.getSource());
            tmp.add(edge.getTarget());
            edgeHelper.add(tmp);
        }
        Set<String> newEdgeSet = new HashSet<>();
        newEdgeSet.add(newEdge.getSource());
        newEdgeSet.add(newEdge.getTarget());
        return edgeHelper.add(newEdgeSet);
    }

    public void deleteCategory(Long gid, String cid) {
        synchronized (this) {
            GraphDTO graphDTO = this.queryById(gid);
            JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
            List<CategoryVO> categories = obj.getJSONArray("categories").toJavaList(CategoryVO.class);
            Optional <CategoryVO> opt = categories.stream().filter(c -> c.getId().equals(cid)).findFirst();
            if (opt.isPresent()) {
                CategoryVO category = opt.get();
                this.dropCategoryViewTable(category.tableName());
                this.deleteAllActionsByParentId(category.getId());
                categories.remove(category);
                obj.put("categories", categories);
                graphDTO.setDataJson(obj.toJSONString());
                this.update(graphDTO);
            }
        }
    }

    public void deleteEdge(Long gid, String eid) {
        synchronized (this) {
            GraphDTO graphDTO = this.queryById(gid);
            JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
            List<EdgeVO> edges = obj.getJSONArray("edges").toJavaList(EdgeVO.class);
            Optional <EdgeVO> opt = edges.stream().filter(e -> e.getId().equals(eid)).findFirst();
            if (opt.isPresent()) {
                edges.remove(opt.get());
                obj.put("edges", edges);
                graphDTO.setDataJson(obj.toJSONString());
                this.update(graphDTO);
            }
        }
    }

    public void batchDeleteCategoriesAndEdges(Long gid, List<String> cids, List<String> eids, GraphVO ctx1, JSONObject context) {
        GraphDTO graphDTO = this.queryById(gid);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        List<CategoryVO> categories = obj.getJSONArray("categories").toJavaList(CategoryVO.class);
        List<CategoryVO> removeCategories = categories.stream().filter(c -> cids.contains(c.getId())).collect(Collectors.toList());
        //清洗操作只删除记录，不删除表，表在撤销栈删除
//        for (CategoryVO category: removeCategories) {
//            this.dropCategoryViewTable(category.tableName());
//            this.deleteAllActionsByParentId(category.getId());
//        }
        for (CategoryVO category: removeCategories) {
            this.deleteActionsRecord(category.getId(), context);
        }
        categories.removeAll(removeCategories);
        List<EdgeVO> edges = obj.getJSONArray("edges").toJavaList(EdgeVO.class);
        List<EdgeVO> removeEdges = edges.stream().filter(e -> eids.contains(e.getId())).collect(Collectors.toList());
        edges.removeAll(removeEdges);
        obj.put("categories", categories);
        obj.put("edges", edges);
        graphDTO.setDataJson(obj.toJSONString());
        this.update(graphDTO);
        if (ctx1 != null) {
            graphDTO.initActionView(ctx1);
            ctx1.getCategories().addAll(removeCategories);
            ctx1.getEdges().addAll(removeEdges);
        }
    }

    public void batchDeleteNodesAndLinks(Long gid, List<String> nids, List<String> lids) {
        GraphDTO graphDTO = this.queryById(gid);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        String graphName = obj.getString("janusGraphName");
        List<NodeVO> nodes = obj.getJSONArray("nodes").toJavaList(NodeVO.class);
        List<LinkVO> links = obj.getJSONArray("links").toJavaList(LinkVO.class);
        List<NodeVO> filterNodes = nodes.stream().filter(n -> !nids.contains(n.getId())).collect(Collectors.toList());
        List<LinkVO> filterLinks = links.stream().filter(l -> !lids.contains(l.getId())).collect(Collectors.toList());
        obj.put("nodes", filterNodes);
        obj.put("links", filterLinks);
        Map<String, String> idMap = obj.getObject(GraphConstant.IDMAP_KEY, Map.class);
        Map<String, String> inverse = HashBiMap.create(idMap).inverse();
        for (String id: nids) {
            inverse.remove(id);
        }
        obj.put(GraphConstant.IDMAP_KEY, idMap);
        graphDTO.setDataJson(obj.toJSONString());
        janusGraphEmbedService.batchDropNodesAndLinks(gid, nids, lids);
        this.update(graphDTO);
    }


    public void updateCategory(Long gid, CategoryVO vo) {
        this.updateCategory(gid, vo, null, null, false);
    }

    public void updateCategory(Long gid, CategoryVO vo, GraphVO ctx1, GraphVO ctx2) {
        this.updateCategory(gid, vo, ctx1, ctx2, false);
    }

    public void updateCategory(Long gid, CategoryVO vo, boolean freezeTableInfo) {
        this.updateCategory(gid, vo, null, null, freezeTableInfo);
    }

    public void updateCategory(Long gid, CategoryVO vo, GraphVO ctx1, GraphVO ctx2, boolean freezeTableInfo) {
        GraphDTO graphDTO = this.queryById(gid);
        this.updateCategory(graphDTO, vo, ctx1, ctx2, freezeTableInfo, true);
        this.update(graphDTO);
    }

    public void updateCategoryWithoutCheck(Long gid, CategoryVO vo) {
        GraphDTO graphDTO = this.queryById(gid);
        this.updateCategory(graphDTO, vo, null, null, false, false);
        this.update(graphDTO);
    }


    public void updateCategory(GraphDTO graphDTO, CategoryVO vo, GraphVO ctx1, GraphVO ctx2, boolean freezeTableInfo, boolean check) {
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        String cid = vo.getId();
        List<CategoryVO> categories = obj.getJSONArray("categories").toJavaList(CategoryVO.class);
        Optional <CategoryVO> opt = categories.stream().filter(c -> c.getId().equals(cid)).findFirst();
        if (opt.isPresent()) {
            CategoryVO oldCategory = opt.get();
            categories.remove(oldCategory);
            if (freezeTableInfo) {
                vo.setTableInfo(oldCategory.getTableInfo());
            }
            categories.add(vo);
            obj.put("categories", categories);
            graphDTO.setDataJson(obj.toJSONString());
            if (ctx1 != null && ctx2 != null){
                graphDTO.initActionView(ctx1);
                graphDTO.initActionView(ctx2);
                ctx1.getCategories().add(oldCategory);
                ctx2.getCategories().add(vo);
            }
        } else if (check) {
            throw DataScienceException.of(BaseErrorCode.GRAPH_ELEMENT_NOT_FOUND, cid);
        } else {
            categories.add(vo);
            obj.put("categories", categories);
            graphDTO.setDataJson(obj.toJSONString());
        }
    }

    public void updateEdge(Long gid, EdgeVO vo) {
        this.updateEdge(gid, vo, null, null);
    }

    public void updateEdgeWithoutCheck(Long gid, EdgeVO vo) {
        GraphDTO graphDTO = this.queryById(gid);
        this.updateEdge(graphDTO, vo, null, null, false);
        this.update(graphDTO);
    }

    public void updateEdge(Long gid, EdgeVO vo, GraphVO ctx1, GraphVO ctx2) {
        GraphDTO graphDTO = this.queryById(gid);
        this.updateEdge(graphDTO, vo, ctx1, ctx2, true);
        this.update(graphDTO);
    }

    public void updateEdge(GraphDTO graphDTO, EdgeVO vo, GraphVO ctx1, GraphVO ctx2, boolean check) {
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        String eid = vo.getId();
        List<EdgeVO> edges = obj.getJSONArray("edges").toJavaList(EdgeVO.class);
        Optional <EdgeVO> opt = edges.stream().filter(e -> e.getId().equals(eid)).findFirst();
        if (opt.isPresent()) {
            EdgeVO oldEdge = opt.get();
            edges.remove(oldEdge);
            edges.add(vo);
            obj.put("edges", edges);
            graphDTO.setDataJson(obj.toJSONString());
            if (ctx1 != null && ctx2 != null){
                graphDTO.initActionView(ctx1);
                graphDTO.initActionView(ctx2);
                ctx1.getEdges().add(oldEdge);
                ctx2.getEdges().add(vo);
            }
        } else if (check) {
            throw DataScienceException.of(BaseErrorCode.GRAPH_ELEMENT_NOT_FOUND, eid);
        } else {
            edges.add(vo);
            obj.put("edges", edges);
            graphDTO.setDataJson(obj.toJSONString());
        }
    }


    public JSONObject queryElementById(Long gid, String id) {
        //element以id的开头字母来区分查找类型
        JSONObject response = new JSONObject();
        GraphDTO graphDTO = this.queryById(gid);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        if (id.startsWith("c")) {
            List<CategoryVO> categories = obj.getJSONArray("categories").toJavaList(CategoryVO.class);
            Optional <CategoryVO> opt = categories.stream().filter(c -> c.getId().equals(id)).findFirst();
            opt.ifPresent(categoryVO -> response.put("category", categoryVO));
        } else if (id.startsWith("e")) {
            List<EdgeVO> edges = obj.getJSONArray("edges").toJavaList(EdgeVO.class);
            Optional <EdgeVO> opt = edges.stream().filter(e -> e.getId().equals(id)).findFirst();
            opt.ifPresent(edgeVO -> response.put("edge", edgeVO));
        } else if (id.startsWith("n")) {
            List<NodeVO> nodes = obj.getJSONArray("nodes").toJavaList(NodeVO.class);
            Optional <NodeVO> opt = nodes.stream().filter(n -> n.getId().equals(id)).findFirst();
            opt.ifPresent(nodeVO -> response.put("node", nodeVO));
        } else if (id.startsWith("l")) {
            List<LinkVO> links = obj.getJSONArray("links").toJavaList(LinkVO.class);
            Optional <LinkVO> opt = links.stream().filter(l -> l.getId().equals(id)).findFirst();
            opt.ifPresent(linkVO -> response.put("link", linkVO));
        }
        return response;
    }

    public TaskDTO syncGraphSchema(Long gid) {
        GraphDTO graphDTO = this.queryById(gid);
        JSONObject graphData = JSONObject.parseObject(graphDTO.getDataJson());
        List<CategoryVO> categories = graphData.getJSONArray("categories").toJavaList(CategoryVO.class);
        List<EdgeVO> edges = graphData.getJSONArray("edges").toJavaList(EdgeVO.class);
        TaskDTO taskDTO = taskService.queryById(graphDTO.getTaskId());
        JSONObject taskData = JSONObject.parseObject(taskDTO.getDataJson());
        taskData.put("categories", categories);
        taskData.put("edges", edges);
        taskData.put("graphId", gid);
        taskDTO.setDataJson(taskData.toJSONString());
        taskService.update(taskDTO);
        return taskDTO;
    }

    public List<List<Object>> queryColumnsFromTable(String sql) {
        //默认第一个为主属性对应字段名
        //两点建立关系
        List<List<Object>> queryResult = new ArrayList<>();
        Connection conn = null;
        ResultSetMetaData m;
        try {
            conn = gpDataProvider.getConn(1L);
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            m = rs.getMetaData();
            int columns = m.getColumnCount();
            //遍历行
            while(rs.next())
            {
                List<Object> row = new ArrayList<>();
                //查询index从1开始
                for (int i=1; i<=columns; i++) {
                    row.add(rs.getObject(i));
                }
                queryResult.add(row);
            }
        } catch (Exception e) {
            logger.error("queryColumnsFromTable conn or query error, sql={}", sql);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    logger.error("queryColumnsFromTable conn close error, sql={}", sql);
                }
            }
        }
        return queryResult;
    }

    public String queryCategoryValue(CategoryVO category) {
        String value = "";
        String tableName = ToolUtil.alignTableName(category.tableName(), 0L);
        String columnName = category.getKeyAttr().getColumn();

        Connection conn = null;
        String sql = String.format("SELECT COUNT(DISTINCT %s) FROM %s WHERE \"_record_id_\" <= 1000000", columnName, tableName);
        try {
            conn = gpDataProvider.getConn(1L);
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            if (rs.next()) {
                value = rs.getString(1);
            }
        } catch (Exception e) {
            logger.error("queryCategoryValue conn or query error, sql={}", sql);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    logger.error("queryCategoryValue conn close error, sql={}", sql);
                }
            }
        }
        return value;
    }

    public void addCategoryAttr(Long gid, String cid, CategoryAttrVO attr, GraphVO ctx1, GraphVO ctx2) {
        GraphDTO graphDTO = this.queryById(gid);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        List<CategoryVO> categories = obj.getJSONArray("categories").toJavaList(CategoryVO.class);
        Optional <CategoryVO> opt = categories.stream().filter(c -> c.getId().equals(cid)).findFirst();
        if (opt.isPresent()) {
            CategoryVO category = opt.get();
            List<CategoryAttrVO> orginAttrs = category.getAttrs();
            orginAttrs.add(attr);
            this.updateCategory(graphDTO, category, ctx1, ctx2, false, true);
            this.update(graphDTO);
        } else {
            throw DataScienceException.of(BaseErrorCode.GRAPH_ELEMENT_NOT_FOUND, cid);
        }
    }

    public void removeCategoryAttr(Long gid, String cid, String attrId, GraphVO ctx1, GraphVO ctx2) {
        GraphDTO graphDTO = this.queryById(gid);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        List<CategoryVO> categories = obj.getJSONArray("categories").toJavaList(CategoryVO.class);
        List<EdgeVO> edges = obj.getJSONArray("edges").toJavaList(EdgeVO.class);
        Optional <CategoryVO> opt = categories.stream().filter(c -> c.getId().equals(cid)).findFirst();
        if (ctx1 != null && ctx2 != null){
            graphDTO.initActionView(ctx1);
            graphDTO.initActionView(ctx2);
        }
        if (opt.isPresent()) {
            CategoryVO category = opt.get();
            CategoryVO oldCategory = SerializationUtils.clone(category);
            List<CategoryAttrVO> orginAttrs = category.getAttrs();
            Optional <CategoryAttrVO> orginAttrOpt = orginAttrs.stream().filter(attr -> attr.getId().equals(attrId)).findFirst();
            String colName = null;
            if (orginAttrOpt.isPresent()) {
                CategoryAttrVO removeAttr = orginAttrOpt.get();
                colName = removeAttr.getColumn();
                orginAttrs.remove(removeAttr);
            } else {
                throw DataScienceException.of(BaseErrorCode.GRAPH_ATTR_NOT_FOUND, attrId);
            }
            if (ctx1 != null && ctx2 != null){
                ctx1.getCategories().add(oldCategory);
                ctx2.getCategories().add(category);
            }
            //关联边查找修改属性
            for (EdgeVO edge: edges) {
                if (edge.getSource().equals(cid) || edge.getTarget().equals(cid)) {
                    EdgeVO oldEdge = SerializationUtils.clone(edge);
                    boolean flag = false;
                    Optional <String> edgeAttrOpt = edge.getAttrIds().stream().filter(id -> id.equals(attrId)).findFirst();
                    if (edgeAttrOpt.isPresent()) {
                        edge.getAttrIds().remove(edgeAttrOpt.get());
                        flag = true;
                    }

                    if (edge.getWeightCfg() != null && !edge.getWeightCfg().isEmpty() && edge.getWeightCfg().getString("attrId").equals(attrId)) {
                        edge.getWeightCfg().remove("attrId");
                        flag = true;
                    }
                    if (flag && ctx1 != null && ctx2 != null){
                        ctx1.getEdges().add(oldEdge);
                        ctx2.getEdges().add(edge);
                    }

                    if (colName != null && edge.getJoinConfigure() != null) {
                        String joinKey = edge.getSource().equals(cid) ? "leftHeaderName" : "rightHeaderName";
                        if (colName.equals(edge.getJoinConfigure().get(joinKey))) {
                            edge.getJoinConfigure().put(joinKey, null);
                        }
                    }
                }
            }
            obj.put("categories", categories);
            obj.put("edges", edges);
            graphDTO.setDataJson(obj.toJSONString());
            this.update(graphDTO);
        } else {
            throw DataScienceException.of(BaseErrorCode.GRAPH_ELEMENT_NOT_FOUND, cid);
        }
    }

    @Deprecated
    public CategoryVO categoryAttrsOP(Long gid, String cid, String opType, CategoryAttrVO attr) {
        CategoryVO vo;
        GraphDTO graphDTO = this.queryById(gid);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        List<CategoryVO> categories = obj.getJSONArray("categories").toJavaList(CategoryVO.class);
        Optional <CategoryVO> opt = categories.stream().filter(c -> c.getId().equals(cid)).findFirst();
        if (opt.isPresent()) {
            vo = opt.get();
        } else {
            return null;
        }
        List<CategoryAttrVO> orginAttrs = vo.getAttrs();
        switch (opType) {
            case "add":
                orginAttrs.add(attr);
                break;
            case "delete":
                IntStream.range(0, orginAttrs.size()).
                        filter(i -> orginAttrs.get(i).getColumn().equals(attr.getColumn())).
                        boxed().findFirst().map(i -> orginAttrs.remove((int) i));
                break;
            case "update":
                IntStream.range(0, orginAttrs.size()).
                        filter(i -> orginAttrs.get(i).getColumn().equals(attr.getColumn())).
                        boxed().findFirst().map(i -> orginAttrs.remove((int) i));
                orginAttrs.add(attr);
                break;
            default:
                return null;
        }
        vo.setAttrs(orginAttrs);
        IntStream.range(0,categories.size()).
                filter(i -> categories.get(i).getId().equals(cid)).
                boxed().findFirst().map(i -> categories.remove((int)i));
        categories.add(vo);
        obj.put("categories", categories);
        graphDTO.setDataJson(obj.toJSONString());
        this.update(graphDTO);
        return vo;
    }

    public Map<Object, GraphAttr> queryAttrDataHelper(CategoryVO category) {
        Map<Object, GraphAttr> result = new HashMap<>();
        String sql = "";
        ResultSetMetaData m;
        List<Integer> sqlType = new ArrayList<>();
        String tableName = ToolUtil.alignTableName(category.tableName(), 0L);
        String keyAttrColumnName = category.getKeyAttr().getColumn();
        String labelColumnName = category.getOriginLabel().getColumn();
        sqlType.add(category.getKeyAttr().getType());
        sqlType.add(category.getOriginLabel().getType());
        if (category.getAttrs().size() == 0 || category.getAttrs() == null) {
                sql = String.format(
                        "SELECT DISTINCT ON (%s) %s AS keyAttr, %s AS labelAttr FROM %s",
                    keyAttrColumnName, keyAttrColumnName, labelColumnName,tableName
            );
        } else {
            List<String> attrColumnNames = category.getAttrs().stream().map(CategoryAttrVO::getColumn).collect(Collectors.toList());
            String sqlAttrCol = attrColumnNames.stream().map(s->"attr."+s).collect(Collectors.joining(","));
            String sqlAttrArrayAggCol = attrColumnNames.stream()
                    .map(s->String.format("ARRAY_TO_STRING(ARRAY_AGG(DISTINCT(%s)), '\",\"') AS %s", s, s))
                    .collect(Collectors.joining(","));
                sql = String.format(
                            "SELECT label.keyAttr, label.labelAttr, %s FROM " +
                            "(SELECT DISTINCT ON (%s) %s AS keyAttr, %s AS labelAttr FROM %s) label " +
                            "INNER JOIN " +
                            "(SELECT %s AS keyAttr, %s FROM %s group by keyAttr) attr " +
                            "ON label.keyAttr=attr.keyAttr",
                    sqlAttrCol,
                    keyAttrColumnName, keyAttrColumnName, labelColumnName,tableName,
                    keyAttrColumnName, sqlAttrArrayAggCol, tableName
            );
            sqlType.addAll(category.getAttrs().stream().map(CategoryAttrVO::getType).collect(Collectors.toList()));
        }


        Connection conn = null;
        try {
            conn = gpDataProvider.getConn(1L);
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            m = rs.getMetaData();
            int columns = m.getColumnCount();
            //遍历行
            while(rs.next())
            {
                //每一行是keyAttrValue,labelName, attrValue...
                GraphAttr graphAttr = new GraphAttr();
                Map<String ,Object> attrMap = new HashMap<>();
                Object keyAttrValue = rs.getObject(1);
                String labelName = rs.getString(2);
                for (int i=3; i<=columns; i++) {
                    Set<?> list = GraphUtil.parseSetFromStr(rs.getString(i), sqlType.get(i - 1));
                    attrMap.put(m.getColumnName(i), list);
                }
                graphAttr.setAttr(attrMap);
                graphAttr.setLabelName(labelName);
                result.put(keyAttrValue, graphAttr);
            }
        } catch (Exception e) {
            logger.error("queryInfoForCategory conn or query error, sql={}", sql);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    logger.error("queryInfoForCategory conn close error, sql={}", sql);
                }
            }
        }
        return result;
    }



    public List<List<String>> queryPath(Long graphId, String srcId, String tarId, JSONObject param) {
        List<List<String>> result = new ArrayList<>();
        List<Path> paths = new ArrayList<>();
        GraphDTO graphDTO = this.queryById(graphId);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        String graphName = obj.getString("janusGraphName");
        String type;
        Integer maxStep;
        if (param == null) {
            type = "shortest";
            maxStep = 10;
        } else {
            type = param.getString("type");
            maxStep = param.getInteger("maxStep");
            if (type == null) {
                type = "shortest";
            }
            if (maxStep == null) {
                maxStep = 10;
            }
        }
        if (type.equals("all")){
            paths = janusGraphEmbedService.allPathBetween2Vertex(graphId, srcId, tarId, maxStep);

        }
        else if (type.equals("shortest")) {
            paths = janusGraphEmbedService.shortestPathBetween2Vertex(graphId, srcId, tarId, maxStep);
        }
        result = paths.stream().map(path->path.objects().stream().map(Object::toString).collect(Collectors.toList())).collect(Collectors.toList());
        return result;
    }

    public Map<String, List<String>> queryCluster(Long graphId, List<String> refIdList, JSONObject param) {
        GraphDTO graphDTO = this.queryById(graphId);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        List<NodeVO> nodes = obj.getJSONArray("nodes").toJavaList(NodeVO.class);
        List<String> removeNodes = nodes.stream().map(NodeVO::getId).filter(id -> !refIdList.contains(id)).collect(Collectors.toList());
        String graphName = obj.getString("janusGraphName");
        if (graphName == null) {
            return null;
        }
        Integer maxIter;
        if (param == null) {
            maxIter = 20;
        } else {
            maxIter = param.getInteger("maxIter");
            if (maxIter == null) {
                maxIter = 20;
            }
        }
        List<String> lids = new ArrayList<>();
        Map<String, String> idMap = obj.getObject("idMap", Map.class);
        JSONObject ret = janusGraphEmbedService.cluster(graphId, removeNodes, lids, maxIter, null, idMap);
        Map result = ret.getObject("result", Map.class);
        if (result == null) {
            return new HashMap<>();
        }

        return result;
    }

    public void batchUpdate(Long graphId, List<NodeVO> nodeVOList, List<LinkVO> linkVOList, List<Float> size) {
        this.batchUpdate(graphId, nodeVOList, linkVOList, size, null, null, null);
    }

    public void batchUpdate(Long graphId, List<NodeVO> nodeVOList, List<LinkVO> linkVOList, List<Float> size, GraphVO ctx1, GraphVO ctx2, JSONObject actionContext) {
        this.batchUpdate(graphId, nodeVOList, linkVOList, size, ctx1, ctx2, actionContext, null);
    }

    public void batchUpdate(Long graphId, List<NodeVO> nodeVOList, List<LinkVO> linkVOList, List<Float> size, GraphVO ctx1, GraphVO ctx2, JSONObject actionContext, Map<String, String> idMapUpdate) {
        GraphDTO graphDTO = this.queryById(graphId);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        List<NodeVO> nodes = obj.getJSONArray("nodes").toJavaList(NodeVO.class);
        List<String> updateNodeIds = nodeVOList.stream().map(NodeVO::getId).collect(Collectors.toList());
        List<NodeVO> newNodeList = nodes.stream().filter(n -> !updateNodeIds.contains(n.getId())).collect(Collectors.toList());
        newNodeList.addAll(nodeVOList);
        obj.put("nodes", newNodeList);

        List<LinkVO> links = obj.getJSONArray("links").toJavaList(LinkVO.class);
        List<String> updateLinkIds = linkVOList.stream().map(LinkVO::getId).collect(Collectors.toList());
        List<LinkVO> newLinkList = links.stream().filter(l -> !updateLinkIds.contains(l.getId())).collect(Collectors.toList());
        newLinkList.addAll(linkVOList);
        obj.put("links", newLinkList);

        if (size != null) {
            obj.put("size", size);
        }
        if (actionContext != null) {
            JSONObject oldActionContext = obj.getJSONObject("actionContext");
            if (oldActionContext == null) {
                oldActionContext = new JSONObject();
            }
            obj.put("actionContext", actionContext);
            if (ctx1 != null && ctx2 != null){
                ctx1.setActionContext(oldActionContext);
                ctx2.setActionContext(actionContext);
            }
        }
        if (idMapUpdate != null) {
            Map<String, String> idMap = obj.getObject(GraphConstant.IDMAP_KEY, Map.class);
            idMap.putAll(idMapUpdate);
            obj.put(GraphConstant.IDMAP_KEY, idMap);
        }
        graphDTO.setDataJson(obj.toJSONString());
        this.update(graphDTO);

        if (ctx1 != null && ctx2 != null){
            graphDTO.initActionView(ctx1);
            graphDTO.initActionView(ctx2);
            List<NodeVO> oldNodeList = nodes.stream().filter(n -> updateNodeIds.contains(n.getId())).collect(Collectors.toList());
            List<LinkVO> oldLinkList = links.stream().filter(l -> updateLinkIds.contains(l.getId())).collect(Collectors.toList());
            ctx1.getNodes().addAll(oldNodeList);
            ctx1.getLinks().addAll(oldLinkList);
            ctx2.getNodes().addAll(nodeVOList);
            ctx2.getLinks().addAll(linkVOList);
        }
    }

    public void batchUpdateNode(Long graphId, List<NodeVO> nodeVOList) {
        GraphDTO graphDTO = this.queryById(graphId);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        List<NodeVO> nodes = obj.getJSONArray("nodes").toJavaList(NodeVO.class);
        List<String> updateIds = nodeVOList.stream().map(NodeVO::getId).collect(Collectors.toList());
        List<NodeVO> newList = nodes.stream().filter(n -> !updateIds.contains(n.getId())).collect(Collectors.toList());
        newList.addAll(nodeVOList);
        obj.put("nodes", newList);
        graphDTO.setDataJson(obj.toJSONString());
        this.update(graphDTO);
    }

    public void batchUpdateLink(Long graphId, List<LinkVO> linkVOList) {
        GraphDTO graphDTO = this.queryById(graphId);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        List<LinkVO> links = obj.getJSONArray("links").toJavaList(LinkVO.class);
        List<String> updateIds = linkVOList.stream().map(LinkVO::getId).collect(Collectors.toList());
        List<LinkVO> newList = links.stream().filter(l -> !updateIds.contains(l.getId())).collect(Collectors.toList());
        newList.addAll(linkVOList);
        obj.put("links", newList);
        graphDTO.setDataJson(obj.toJSONString());
        this.update(graphDTO);
    }

    public void updateNode(Long gid, NodeVO vo) {
        GraphDTO graphDTO = this.queryById(gid);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        String nid = vo.getId();
        List<NodeVO> nodes = obj.getJSONArray("nodes").toJavaList(NodeVO.class);
        Optional <NodeVO> opt = nodes.stream().filter(n -> n.getId().equals(nid)).findFirst();
        if (opt.isPresent()) {
            nodes.remove(opt.get());
            nodes.add(vo);
            obj.put("nodes", nodes);
            graphDTO.setDataJson(obj.toJSONString());
            this.update(graphDTO);
        } else {
            throw DataScienceException.of(BaseErrorCode.GRAPH_ELEMENT_NOT_FOUND, nid);
        }
    }

    public void updateLink(Long gid, LinkVO vo) {
        GraphDTO graphDTO = this.queryById(gid);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        String lid = vo.getId();
        List<LinkVO> links = obj.getJSONArray("links").toJavaList(LinkVO.class);
        Optional <LinkVO> opt = links.stream().filter(l -> l.getId().equals(lid)).findFirst();
        if (opt.isPresent()) {
            links.remove(opt.get());
            links.add(vo);
            obj.put("links", links);
            graphDTO.setDataJson(obj.toJSONString());
            this.update(graphDTO);
        } else {
            throw DataScienceException.of(BaseErrorCode.GRAPH_ELEMENT_NOT_FOUND, lid);
        }
    }

    public void deleteNode(Long gid, String nid) {
        synchronized (this) {
            GraphDTO graphDTO = this.queryById(gid);
            JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
            String graphName = obj.getString("janusGraphName");
            List<NodeVO> nodes = obj.getJSONArray("nodes").toJavaList(NodeVO.class);
            Optional <NodeVO> opt = nodes.stream().filter(n -> n.getId().equals(nid)).findFirst();
            if (opt.isPresent()) {
                nodes.remove(opt.get());
                obj.put("nodes", nodes);
                graphDTO.setDataJson(obj.toJSONString());
                this.update(graphDTO);
                janusGraphEmbedService.dropNode(gid, nid);
            }
        }
    }

    public void deleteLink(Long gid, String lid) {
        synchronized (this) {
            GraphDTO graphDTO = this.queryById(gid);
            JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
            String graphName = obj.getString("janusGraphName");
            List<LinkVO> links = obj.getJSONArray("links").toJavaList(LinkVO.class);
            Optional <LinkVO> opt = links.stream().filter(l -> l.getId().equals(lid)).findFirst();
            if (opt.isPresent()) {
                links.remove(opt.get());
                obj.put("links", links);
                graphDTO.setDataJson(obj.toJSONString());
                this.update(graphDTO);
                janusGraphEmbedService.dropLink(gid, lid);
            }
        }
    }

    public Long saveWidget(Long gid, String type, String graphName) {
        GraphDTO graphDTO = this.queryById(gid);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());

        JSONObject widgetDataJson = new JSONObject();
        widgetDataJson.put("graphId", gid);
        widgetDataJson.put("categories", obj.getJSONArray("categories").toJavaList(CategoryVO.class));
        widgetDataJson.put("edges", obj.getJSONArray("edges").toJavaList(EdgeVO.class));
        widgetDataJson.put("nodes", obj.getJSONArray("nodes").toJavaList(NodeVO.class));
        widgetDataJson.put("links", obj.getJSONArray("links").toJavaList(LinkVO.class));
        if (obj.containsKey("size")) {
            widgetDataJson.put("size", obj.getJSONArray("size").toJavaList(Float.class));
        } else {
            widgetDataJson.put("size", Arrays.asList(960f, 500f));
        }
        WidgetDTO widget = new WidgetDTO();
        widget.setTid(graphDTO.getTaskId());
        widget.setType(type);
        if (graphName == null) {
            widget.setName(graphDTO.getName());
        } else {
            widget.setName(graphName);
        }
        widget.setDataJson(widgetDataJson.toJSONString());

        Long id = widgetService.save(widget);
        JSONArray widgets =  obj.getJSONArray("widgets");
        if (CollectionUtil.isEmpty(widgets)) {
            widgets = new JSONArray();
        }
        widgets.add(id);
        obj.put("widgets", widgets);
        graphDTO.setDataJson(obj.toJSONString());
        graphMapper.update(graphDTO);
        return id;
    }

    public JSONObject queryEdgeConfigure(Long gid, String eid) {
        JSONObject result = new JSONObject();
        GraphDTO graphDTO = this.queryById(gid);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        List<CategoryVO> categories = obj.getJSONArray("categories").toJavaList(CategoryVO.class);
        List<EdgeVO> edges = obj.getJSONArray("edges").toJavaList(EdgeVO.class);
        Optional<EdgeVO> edgeOpt = edges.stream().filter(e->e.getId().equals(eid)).findFirst();
        if (!edgeOpt.isPresent()) {
            throw DataScienceException.of(BaseErrorCode.GRAPH_ELEMENT_NOT_FOUND, eid);
        }
        EdgeVO edge = edgeOpt.get();
        String srcId = edge.getSource();
        String tarId = edge.getTarget();
        CategoryVO srcCategory = categories.stream().filter(c->c.getId().equals(srcId)).findFirst().get();
        CategoryVO tarCategory = categories.stream().filter(c->c.getId().equals(tarId)).findFirst().get();
        String leftTableName = srcCategory.tableName();
        String rightTableName = tarCategory.tableName();
        JSONObject leftSemantics = srcCategory.getTableInfo().getJSONObject("semantic");
        JSONObject rightSemantics = tarCategory.getTableInfo().getJSONObject("semantic");

        JSONArray recommendResult = joinRecommend(gid, leftTableName, rightTableName, leftSemantics, rightSemantics);

        //根据节点属性做筛选
        List<String> leftAttrs = new ArrayList<>();
        List<String> rightAttrs = new ArrayList<>();
        leftAttrs.add(srcCategory.getKeyAttr().getColumn());
        rightAttrs.add(tarCategory.getKeyAttr().getColumn());
        leftAttrs.addAll(srcCategory.getAttrs().stream().map(CategoryAttrVO::getColumn).collect(Collectors.toList()));
        rightAttrs.addAll(tarCategory.getAttrs().stream().map(CategoryAttrVO::getColumn).collect(Collectors.toList()));

        JSONArray filterRecommendResult = new JSONArray();
        for (int i = 0; i < recommendResult.size(); i++) {
            JSONObject jsonObject = recommendResult.getJSONObject(i);
            if (leftAttrs.contains(jsonObject.getString("leftHeaderName"))
                    && rightAttrs.contains(jsonObject.getString("rightHeaderName"))) {
                filterRecommendResult.add(jsonObject);
            }
        }

        result.put("leftTableName", leftTableName);
        result.put("rightTableName", rightTableName);
        result.put("autoJoinRecommendation", filterRecommendResult);
        JSONObject conf = edge.getJoinConfigure();
        if (conf == null) {
            if (filterRecommendResult.size() != 0) {
                result.put("leftHeaderName", filterRecommendResult.getJSONObject(0).getString("leftHeaderName"));
                result.put("rightHeaderName", filterRecommendResult.getJSONObject(0).getString("rightHeaderName"));
            }
        }
        else if (conf.getString("leftHeaderName") == null || conf.getString("rightHeaderName") == null) {
            String key1;
            String key2;
            if (conf.getString("leftHeaderName") == null) {
                key1 = "leftHeaderName";
                key2 = "rightHeaderName";
            } else {
                key1 = "rightHeaderName";
                key2 = "leftHeaderName";
            }
            for (int i = 0; i < filterRecommendResult.size(); i++) {
                JSONObject recommend = filterRecommendResult.getJSONObject(i);
                if (recommend.getString(key2).equals(conf.getString(key2))) {
                    result.put(key1, recommend.getString(key1));
                    break;
                }
            }
            result.put(key2, conf.getString(key2));
        }
        else {
            result.put("leftHeaderName", conf.getString("leftHeaderName"));
            result.put("rightHeaderName", conf.getString("rightHeaderName"));
        }
        return result;
    }

    public void saveEdgeConfigure(Long gid, String eid, String leftHeaderName, String rightHeaderName, GraphVO ctx1, GraphVO ctx2) {
        JSONObject conf = new JSONObject();
        GraphDTO graphDTO = this.queryById(gid);
        JSONObject obj = JSONObject.parseObject(graphDTO.getDataJson());
        List<EdgeVO> edges = obj.getJSONArray("edges").toJavaList(EdgeVO.class);
        Optional<EdgeVO> edgeOpt = edges.stream().filter(e->e.getId().equals(eid)).findFirst();
        if (!edgeOpt.isPresent()) {
            throw DataScienceException.of(BaseErrorCode.GRAPH_ELEMENT_NOT_FOUND, eid);
        }
        EdgeVO edge = edgeOpt.get();
        EdgeVO oldEdge = SerializationUtils.clone(edge);
        conf.put("leftHeaderName", leftHeaderName);
        conf.put("rightHeaderName", rightHeaderName);
        edge.setJoinConfigure(conf);
        obj.put("edges", edges);
        graphDTO.setDataJson(obj.toJSONString());
        this.update(graphDTO);
        if (ctx1 != null && ctx2 != null){
            graphDTO.initActionView(ctx1);
            graphDTO.initActionView(ctx2);
            ctx1.getEdges().add(oldEdge);
            ctx2.getEdges().add(edge);
        }
    }

    public JSONArray joinRecommend(Long gid, String leftTableName, String rightTableName, JSONObject leftSemantics, JSONObject rightSemantics) {
        if (leftSemantics == null | rightSemantics == null) {
            return null;
        }
        leftTableName = ToolUtil.alignTableName(leftTableName, 0L);
        rightTableName = ToolUtil.alignTableName(rightTableName, 0L);
        return autoJoinService.autoJoinRecommend(
                leftTableName, rightTableName,
                leftSemantics, rightSemantics,
                fastTextService.getAutojoinModel());
    }

    public String createCategoryViewTable(String tableName, String cid) {
        String viewTableName = String.format(VIEW_TABLE_NAME, cid, System.currentTimeMillis());
        Connection conn = null;
        Statement st = null;
        String selectSql = String.format("SELECT * FROM %s", ToolUtil.alignTableName(tableName, 0));
        String sql = String.format(CREATE_VIEW_SQL, viewTableName, selectSql);
        try {
            conn = gpDataProvider.getConn(1L);
            st = conn.createStatement();
            st.execute(sql);
        } catch (Exception e) {
            return null;
        } finally {
            JDBCUtil.close(conn, null, null);
        }
        return viewTableName;
    }

    public void dropCategoryViewTable(String tableName) {
        String sql = String.format(DROP_VIEW_SQL, tableName);
        Connection conn = null;
        Statement st = null;
        try {
            conn = gpDataProvider.getConn(1L);
            st = conn.createStatement();
            st.execute(sql);
        } catch (Exception e) {
        } finally {
            JDBCUtil.close(conn, null, null);
        }
    }

    /**
     * 1、查出之前action记录，
     * 2、删除之前的，增加新的
     * 3、用原来的参数重新生成新的视图表
     */
    @Transactional(rollbackFor = Exception.class)
    public CategoryCleanAction addAction(Long gid, String cid, JSONObject data, List<JSONObject> ctx1) {
        CategoryVO currentCategory = this.getCategoryByCId(gid, cid);
        TaskInstanceVO action = this.toAction(gid, cid, data);
        JSONObject actionData = action.getData();
        CategoryVO oldCategory = SerializationUtils.clone(currentCategory);
        List<TaskInstanceDTO> actions = graphMapper.queryActionByParentIdAndOrder(cid);
        //先删除action操作
        this.clearActions(actions, cid);
        //将当前要添加的action加入到action列表中
        int index = 0;
        boolean flag = false;
        for (int i = 0; i < actions.size(); i++) {
            TaskInstanceDTO previousAction = actions.get(i);
            JSONObject previousData = JSONObject.parseObject(previousAction.getDataJson());
            if (previousData.getLong("id").equals(actionData.getLong("id"))) {
                index = i;
                flag = true;
            }
        }
        if (!flag) {
            logger.error(ApiResultCode.GRAPH_CATEGORY_CLEAN_ACTION_ID_NOT_FOUND.getMessage() + " " + actionData.getLong("id"));
            return null;
        }
        actions.add(index + 1, action.toTask(action));
        //重新生成action
        this.rebuildActions(actions);
        TaskInstanceDTO addAction = actions.get(index + 1);
        this.updateCategoryInfo(gid, currentCategory, actions, index + 1);
        JSONObject item = (JSONObject)JSONObject.toJSON(addAction);
        item.put("index", index + 1);
        item.put("category", oldCategory);
        ctx1.add(item);
        return new CategoryCleanAction(addAction.view(), currentCategory);
    }

    public void clearActions(List<TaskInstanceDTO> actions, String cId) {
        //删除第一条之后的view表
        //删除所有action记录
        if (actions.size() > 1) {
            Long id = actions.get(1).getId();//清理第二条生成的表，级联删除
            TaskInstanceDTO taskInstanceDTO = taskInstanceService.queryById(id);
            String table = JSONObject.parseObject(taskInstanceDTO.getDataJson()).getString("table");
            gpDataProvider.dropRedundantTables(table, true);
        }
        this.deleteActionsRecord(cId, actions.get(0).getId());
    }

    public String deleteActionsRecord(String parentId, Long id) {
        Map map = Maps.newHashMap();
        map.put("parentId", parentId);
        map.put("id", id);
        TaskInstanceDTO taskInstanceDTO = taskInstanceService.queryById(id);
        if (null != taskInstanceDTO) {
            String table = JSONObject.parseObject(taskInstanceDTO.getDataJson()).getString("table");
            graphMapper.deleteActionsByParentIdById(map);
            return table;
        }
        return null;
    }

    public String deleteActionsRecord(String cid, JSONObject context) {
        //删除时记录当前action信息
        List<TaskInstanceDTO> actions = graphMapper.queryActionByParentIdAndOrder(cid);
        JSONObject contextActions = context.getJSONObject("cleanActions");
        if (CollectionUtil.isNotEmpty(contextActions)) {
            contextActions.put(cid, actions);
        } else {
            JSONObject obj = new JSONObject();
            obj.put(cid, actions);
            context.put("cleanActions", obj);
        }
        if (actions.size() > 0) {
            Long id = actions.get(0).getId();
            TaskInstanceDTO taskInstanceDTO = taskInstanceService.queryById(id);
            String table = JSONObject.parseObject(taskInstanceDTO.getDataJson()).getString("table");
            JSONArray array = context.getJSONArray("views");
            if (CollectionUtil.isNotEmpty(array)) {
                array.add(table);
            } else {
                JSONArray views = new JSONArray();
                views.add(table);
                context.put("views", views);
            }
        }
        //删除action记录
        return this.deleteActionsRecord(cid, actions.get(0).getId());
    }

    public String rebuildActions(List<TaskInstanceDTO> actions) {
        String table = JSONObject.parseObject(actions.get(0).getDataJson()).getString("table");
        String logInfo = null;
        String outPutTable = table;
        boolean status = true;

        Map<Long, Long> actionMap = Maps.newHashMap();
        List<TaskInstanceDTO> tempList = Lists.newArrayList();
        if (CollectionUtil.isNotEmpty(actions)) {
            //重新生成action
            for (TaskInstanceDTO action : actions) {
                JSONObject actionData = JSONObject.parseObject(action.getDataJson());
                actionData.put("table", table);
                TaskInstanceVO view = action.view();
                TaskInstanceDTO newAction = tColumnService.addOneAction(actionData, view);
                if (status && null != newAction) {
                    actionMap.putIfAbsent(view.getData().getLong("id"),
                            JSONObject.parseObject(newAction.getDataJson()).getLong("id"));
                    tempList.add(newAction);
                    //添加action失败，记录第一次失败的信息
                    if (TaskInstanceStatus.FAIL.toString().equals(newAction.getStatus())) {
                        status = false;
                        logInfo = newAction.getLogInfo();
                    } else {//添加action成功，记录输出表
                        outPutTable = actionData.getString("table");
                    }
                }
                table = actionData.getString("table");
            }
            actions.clear();
            actions.addAll(tempList);
        }
        return outPutTable;
    }

    public TaskInstanceVO toAction(Long gid, String cid, JSONObject data) {
        GraphDTO graph = this.queryById(gid);
        TaskInstanceVO vo = new TaskInstanceVO();
        vo.setParentId(cid);
        vo.setTaskId(graph.getTaskId());
        vo.setPipelineId(graph.getPipelineId());
        vo.setProjectId(graph.getProjectId());
        vo.setUserId(graph.getUserId());
        vo.setData(data);
        vo.setTaskName("图构建节点清洗");
        return vo;
    }

    /**
     * 添加一条原始数据操作记录
     */
    public TaskInstanceVO addOriginalAction(GraphDTO graph, CategoryVO category) {
        TaskInstanceVO vo = new TaskInstanceVO();
        vo.setParentId(category.getId());
        vo.setTaskId(graph.getTaskId());
        vo.setPipelineId(graph.getPipelineId());
        vo.setProjectId(graph.getProjectId());
        vo.setTaskName("图构建节点清洗");
        vo.setUserId(JwtUtil.getCurrentUserId());

        JSONObject data = new JSONObject();
        data.put("action", ActionEnum.ORIGINAL_DATA.toString());
        data.put("table", category.tableName());
        data.put("description", ActionEnum.ORIGINAL_DATA.label());
        data.put("originalCategory", category);
        vo.setData(data);
        TaskInstanceDTO newAction = tColumnService.addOneAction(data, vo);
        return newAction.view();
    }

    /**
     * 查询action列表
     */
    public List<CategoryCleanAction> queryAction(String cid) {
        List<TaskInstanceDTO> taskInstanceDTOS = graphMapper.queryActionByParentIdAndOrder(cid);
        List<CategoryCleanAction> result = Lists.newArrayList();
        if (CollectionUtil.isNotEmpty(taskInstanceDTOS)) {
            taskInstanceDTOS.forEach(taskInstanceDTO -> result.add(new CategoryCleanAction(taskInstanceDTO.view())));
        }
        return result;
    }

    /**
     * 删除节点时清除所有该节点action记录和view表
     */
    public void deleteAllActionsByParentId(String cid) {
        List<TaskInstanceDTO> actions = graphMapper.queryActionByParentIdAndOrder(cid);
        this.clearActions(actions, cid);
    }

    @Transactional(rollbackFor = Exception.class)
    public CategoryCleanAction deleteActions(Long gid, String cid, Long id, List<JSONObject> ctx1) {
        CategoryVO currentCategory = this.getCategoryByCId(gid, cid);
        List<TaskInstanceDTO> actions = graphMapper.queryActionByParentIdAndOrder(cid);
        if (CollectionUtil.isNotEmpty(actions) && actions.get(0).getId().equals(id)) {
            throw new DataScienceException("原始数据无法删除");
        }
        int index = 0;
        for (int i = 0; i < actions.size(); i++) {
            TaskInstanceDTO dto = actions.get(i);
            JSONObject data = JSONObject.parseObject(dto.getDataJson());
            Long actionId = data.getLong("id");
            if (actionId.equals(id)) {
                index = i;
            }
        }
        if (index == 0) {
            //在列表中找不到要删除的那条。可能是重复点击了
            logger.error(ApiResultCode.GRAPH_CATEGORY_CLEAN_ACTION_ID_NOT_FOUND.getMessage() + " " + id);
            return null;
        }
        this.clearActions(actions, cid);
        TaskInstanceDTO remove = actions.remove(index);
        CategoryVO oldCategory = SerializationUtils.clone(currentCategory);
        this.rebuildActions(actions);
        JSONObject item = (JSONObject)JSONObject.toJSON(remove);
        item.put("index", index);
        item.put("category", oldCategory);
        ctx1.add(item);
        //默认switch到最后成功那一条
        index = actions.size() - 1;
        this.updateCategoryInfo(gid, currentCategory, actions, index);
        TaskInstanceDTO addAction = actions.get(index);
        return new CategoryCleanAction(addAction.view(), currentCategory);
    }

    @Transactional(rollbackFor = Exception.class)
    public CategoryCleanAction updateAction(Long gid, String cid, JSONObject data, List<JSONObject> ctx1, List<JSONObject> ctx2) {
        CategoryVO currentCategory = this.getCategoryByCId(gid, cid);
        Long id = data.getLong("id");
        List<TaskInstanceDTO> actions = graphMapper.queryActionByParentIdAndOrder(cid);
        if (actions.isEmpty() || actions.get(0).getId().equals(id)) {
            throw new DataScienceException("原始数据无法修改");
        }
        int index = 0;
        for (int i = 0; i < actions.size(); i++) {
            TaskInstanceDTO dto = actions.get(i);
            JSONObject jsonData = JSONObject.parseObject(dto.getDataJson());
            Long actionId = jsonData.getLong("id");
            if (actionId.equals(id)) {
                index = i;
            }
        }
        if (index == 0) {
            //在列表中找不到要删除的那条。可能是重复点击了
            logger.error(ApiResultCode.GRAPH_CATEGORY_CLEAN_ACTION_ID_NOT_FOUND.getMessage() + " " + id);
            return null;
        }
        this.clearActions(actions, cid);
        TaskInstanceDTO oldAction = actions.remove(index);
        CategoryVO oldCategory = SerializationUtils.clone(currentCategory);

        JSONObject item1 = (JSONObject)JSONObject.toJSON(oldAction);
        item1.put("index", index);
        item1.put("category", oldCategory);
        ctx1.add(item1);

        oldAction.setDataJson(data.toJSONString());
        actions.add(index, oldAction);
        this.rebuildActions(actions);
        TaskInstanceDTO addAction = actions.get(index);
        //任务成功更新其他信息
        this.updateCategoryInfo(gid, currentCategory, actions, index);

        JSONObject item2 = (JSONObject)JSONObject.toJSON(addAction);
        item2.put("index", index);
        ctx2.add(item2);

        return new CategoryCleanAction(addAction.view(), currentCategory);
    }

    public CategoryVO updateSemantic(Long gid, String cid, JSONObject data){
        //语义修改不产生历史记录
        CategoryVO currentCategory = this.getCategoryByCId(gid, cid);
        JSONObject tableInfo = currentCategory.getTableInfo();
        JSONObject semantics = tableInfo.getJSONObject("semantic");
        String col = data.getString("col");
        String semantic = data.getString("semantic");
        semantics.put(col, semantic);
        currentCategory.setTableInfo(tableInfo);
        this.updateCategory(gid, currentCategory);
        return currentCategory;
    }

    public void updateCategoryInfo(Long gid, CategoryVO currentCategory, List<TaskInstanceDTO> actions, int index) {
        TaskInstanceVO originAction = actions.get(0).view();
        assert "ORIGINAL_DATA".equals(originAction.getData().getString("action"));
        CategoryVO originCategory = originAction.getData().getObject("originalCategory", CategoryVO.class);
        //用currentCategory固定样式，originCategory更新节点相关信息
        for (int i = 1; i < actions.size(); i++) {

            TaskInstanceVO addAction = actions.get(i).view();
            String addActionStatus = addAction.getData().getString("status");

            if ("FAIL".equals(addActionStatus)) {
                //遇到失败任务不往下更新
                break;
            } else if ("SUCCESS".equals(addActionStatus)) {
                JSONObject actionData = addAction.getData();
                //重名名字段修改语义，修改节点属性信息
                if ("RENAME".equals(actionData.getString("action"))) {
                    String col = actionData.getString("col");
                    String newCol = actionData.getString("newCol");
                    CategoryAttrVO keyAttr = originCategory.getKeyAttr();
                    if (keyAttr.getColumn().equals(col)) {
                        keyAttr.setColumn(newCol);
                        keyAttr.setName(newCol);
                    }
                    List<CategoryAttrVO> attrs = originCategory.getAttrs();
                    for (CategoryAttrVO attr: attrs) {
                        if (attr.getColumn().equals(col)) {
                            attr.setColumn(newCol);
                            attr.setName(newCol);
                            originCategory.setAttrs(attrs);
                        }
                    }
                    CategoryAttrVO originLabel = originCategory.getOriginLabel();
                    if (originLabel.getColumn().equals(col)) {
                        originLabel.setColumn(newCol);
                        originLabel.setName(newCol);
                    }
                    JSONObject tableInfo = originCategory.getTableInfo();
                    JSONObject semantics = tableInfo.getJSONObject("semantic");
                    if (CollectionUtil.isNotEmpty(semantics)) {
                        semantics.put(newCol, semantics.getString(col));
                        originCategory.setTableInfo(tableInfo);
                    }
                }

                //类型更改修改，修改节点属性类型
                if ("TYPE_TRANSFORM".equals(actionData.getString("action"))) {
                    String col = actionData.getString("col");
                    String totype = actionData.getString("toType");
                    int totypeInt = GraphUtil.dataCleanType2sqlType(totype);

                    CategoryAttrVO keyAttr = originCategory.getKeyAttr();
                    if (keyAttr.getColumn().equals(col)) {
                        keyAttr.setType(totypeInt);
                    }
                    List<CategoryAttrVO> attrs = originCategory.getAttrs();
                    for (CategoryAttrVO attr: attrs) {
                        if (attr.getColumn().equals(col)) {
                            attr.setType(totypeInt);
                            originCategory.setAttrs(attrs);
                        }
                    }
                    CategoryAttrVO originLabel = originCategory.getOriginLabel();
                    if (originLabel.getColumn().equals(col)) {
                        originLabel.setType(totypeInt);
                    }
                }
            }
            //更新节点表名
            JSONObject tableInfo = originCategory.getTableInfo();
            tableInfo.put("tableName", addAction.getData().getString("table"));
            if (addAction.getData().getString("action").equals("FILTER")) {
                originCategory.setValue(queryCategoryValue(originCategory));
            }
        }
        currentCategory.setKeyAttr(originCategory.getKeyAttr());
        currentCategory.setAttrs(originCategory.getAttrs());
        currentCategory.setOriginLabel(originCategory.getOriginLabel());
        currentCategory.setTableInfo(originCategory.getTableInfo());
        currentCategory.setValue(originCategory.getValue());

        this.updateCategory(gid, currentCategory);

    }

    public boolean downloadData(HttpServletResponse response, GraphDTO graph, String format, boolean isBuild) {
        Long graphId = graph.getId();
        JSONObject obj = JSONObject.parseObject(graph.getDataJson());
        List<NodeVO> nodes = obj.getJSONArray("nodes").toJavaList(NodeVO.class);
        List<LinkVO> links = obj.getJSONArray("links").toJavaList(LinkVO.class);
        if (nodes.size() == 0) {
            return false;
        }
        List<MetricResult> metricResults = new ArrayList<>();
        MetricResultManager metricResultManager = obj.getObject("metricResultManager", MetricResultManager.class);
        if (metricResultManager != null) {
            Map<String, Boolean> activateMap = metricResultManager.getActivateMap();
            List<String> metrics = new ArrayList<>();
            for (String key: activateMap.keySet()) {
                if (activateMap.get(key)) {
                    metrics.add(key);
                }
            }
            metricResults = metricResultManager.retrieveFromTag(metrics);
        }


        String content = "";
        switch (format) {
            case "csv":
                Long filePrefix = System.currentTimeMillis();
                response.setContentType("application/x-zip-compressed;charset=UTF-8");
                response.setHeader("Content-disposition","attachment;filename=\"download_" + filePrefix + ".zip\"");
                StringBuilder nodeSb = new StringBuilder();
                StringBuilder linkSb = new StringBuilder();
                ExporterCSV exporterCSV = new ExporterCSV();
                exporterCSV.setNodeSb(nodeSb);
                exporterCSV.setLinkSb(linkSb);
                exporterCSV.setGraphId(graphId);
                exporterCSV.setNodes(nodes);
                exporterCSV.setLinks(links);
                exporterCSV.setMetricResults(metricResults);
                if (isBuild) {
                    JSONObject multiValueHelper = obj.getJSONObject("multiValueHelper");
                    List<CategoryVO> categories = obj.getJSONArray("categories").toJavaList(CategoryVO.class);
                    Map<String, String> labelMap = categories.stream().collect(Collectors.toMap(CategoryVO::getId, CategoryVO::getLabel));
                    exporterCSV.setMultiValueHelper(multiValueHelper);
                    exporterCSV.setLabelMap(labelMap);
                }
                exporterCSV.execute();
                String nodeCSV = nodeSb.toString();
                String linkCSV = linkSb.toString();
                try {
                    ZipOutputStream zos = new ZipOutputStream(response.getOutputStream());
                    ZipEntry zipEntry1 = new ZipEntry("node_" + filePrefix + ".csv");
                    zos.putNextEntry(zipEntry1);
                    zos.write(new byte []{( byte ) 0xEF ,( byte ) 0xBB ,( byte ) 0xBF });
                    zos.write(nodeCSV.getBytes(StandardCharsets.UTF_8));

                    ZipEntry zipEntry2 = new ZipEntry("link_" + filePrefix + ".csv");
                    zos.putNextEntry(zipEntry2);
                    zos.write(new byte []{( byte ) 0xEF ,( byte ) 0xBB ,( byte ) 0xBF });
                    zos.write(linkCSV.getBytes(StandardCharsets.UTF_8));
                    zos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                    return false;
                }
                return true;
            case "json":
                response.setContentType("application/json;charset=UTF-8");
                response.setHeader("Content-disposition","attachment;filename=\"download_" + System.currentTimeMillis() + ".json\"");
                ExporterJSON exporterJSON = new ExporterJSON();
                exporterJSON.setGraphId(graphId);
                exporterJSON.setNodes(nodes);
                exporterJSON.setLinks(links);
                exporterJSON.setMetricResults(metricResults);
                if (isBuild) {
                    JSONObject multiValueHelper = obj.getJSONObject("multiValueHelper");
                    List<CategoryVO> categories = obj.getJSONArray("categories").toJavaList(CategoryVO.class);
                    Map<String, String> labelMap = categories.stream().collect(Collectors.toMap(CategoryVO::getId, CategoryVO::getLabel));
                    exporterJSON.setMultiValueHelper(multiValueHelper);
                    exporterJSON.setLabelMap(labelMap);
                }
                exporterJSON.execute();
                content = exporterJSON.getJSONString();
                break;
            case "gml":
                response.setContentType("text/plain;charset=UTF-8");
                response.setHeader("Content-disposition","attachment;filename=\"download_" + System.currentTimeMillis() + ".gml\"");
                StringWriter stringWriter = new StringWriter();
                ExporterGML exporterGML = new ExporterGML();
                exporterGML.setWriter(stringWriter);
                exporterGML.setGraphId(graphId);
                exporterGML.setNodes(nodes);
                exporterGML.setLinks(links);
                exporterGML.setMetricResults(metricResults);
                if (isBuild) {
                    JSONObject multiValueHelper = obj.getJSONObject("multiValueHelper");
                    List<CategoryVO> categories = obj.getJSONArray("categories").toJavaList(CategoryVO.class);
                    Map<String, String> labelMap = categories.stream().collect(Collectors.toMap(CategoryVO::getId, CategoryVO::getLabel));
                    exporterGML.setMultiValueHelper(multiValueHelper);
                    exporterGML.setLabelMap(labelMap);
                }
                exporterGML.execute();
                content = stringWriter.toString();
                break;
            default:
                return false;
        }

        OutputStream outputStream;
        try {
            outputStream = response.getOutputStream();
            outputStream.write(content.getBytes(StandardCharsets.UTF_8));
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    public void checkAuth(Long graphId) {
        long userId = JwtUtil.getCurrentUserId();
        if (userId==0) {
            throw new  DataScienceException(BaseErrorCode.UNAUTHORIZED);
        }

        Long owner = queryUserById(graphId);
        if (owner == null || JwtUtil.getCurrentUserId() != owner) {
            throw new DataScienceException(BaseErrorCode.GRAPH_NO_PERMISSION);
        }
    }

    public void checkGraphAndProject(Long graphId, Long projectId) {
        //检查从属关系
        Long project = queryProjectById(graphId);
        if (project == null) {
            throw new DataScienceException(BaseErrorCode.UNAUTHORIZED);
        }
        if (!project.equals(projectId)) {
            throw new DataScienceException(BaseErrorCode.GRAPH_PROJECT_RELATION_ERROR);
        }
    }
}
